package oracle

import (
	"bytes"
	"context"
	"database/sql"
	"fmt"
	"reflect"
	"strings"
	"time"

	"github.com/pkg/errors"
)

type BuildResult struct {
	ExecSql string
	Params  []any
}

func (oraDB *OraDB) BuildQuery(ctx context.Context, entity interface{},
	tableName, whereSql string, args ...any) (*BuildResult, error) {
	tableInfo, err := QueryTableInfo(entity)
	if err != nil {
		oraDB.logger.Errorf("QueryTableInfo got error[%v]", err)
		return nil, err
	}

	if tableName == "" {
		if oraDB.schema != "" {
			tableName = oraDB.schema + "." + tableInfo.TableName
		} else {
			tableName = tableInfo.TableName
		}
	} else {
		if oraDB.schema != "" {
			tableName = oraDB.schema + "." + tableName
		}
	}

	querySql := fmt.Sprintf("SELECT %s FROM %s ",
		strings.Join(tableInfo.QueryColumns, ","), tableName)

	qBr := &BuildResult{
		ExecSql: querySql,
		Params:  args,
	}
	if whereSql != "" {
		br, err := oraDB.BuildCondition(ctx, whereSql, args...)
		if err != nil {
			oraDB.logger.Errorc(ctx, "BuildCondition 处理失败[%s][%s]", whereSql, err)
			return nil, err
		}

		querySql += fmt.Sprintf("WHERE %s", br.ExecSql)
		qBr.ExecSql = querySql
		qBr.Params = br.Params
	}
	return qBr, nil
}

func (oraDB *OraDB) BuildCondition(ctx context.Context,
	whereSql string, args ...any) (*BuildResult, error) {
	if whereSql == "" {
		return &BuildResult{
			ExecSql: "",
			Params:  nil,
		}, nil
	}

	// 查找绑定变量，根据绑定变量匹配参数
	paramCnt := len(args)
	for _, v := range args {
		if v == nil {
			return nil, errors.Errorf("BuildCondition: 传入参数存在nil参数；")
		}
	}
	if paramCnt == 0 {
		return nil, errors.Errorf("BuildCondition: 传入参数为空;")
	}

	idx := 0
	params := make([]any, paramCnt)
	vars := make([]int, paramCnt)

	list := strings.Split(whereSql, " ")
	newList := make([]string, 0)
	for _, v := range list {
		if v != "" {
			newList = append(newList, v)
		}
	}
	list = newList

	for i, v := range list {
		if v == "" {
			continue
		}
		if strings.Contains(v, "?") || v[0] == ':' { // 通配占位符
			if idx >= paramCnt {
				return nil, errors.Errorf("宿主变量[%s][%d] 和传入参数不匹配[%d]",
					whereSql, idx, paramCnt)
			}
			params[idx] = args[idx]
			vars[idx] = i
			idx++
		}
	}

	if idx == 0 {
		return &BuildResult{
			ExecSql: whereSql,
		}, nil
	}

	rIdx := 1
	rArgs := make([]any, 0)
	for k, v := range vars {
		if strings.Contains(list[v], "?") { // 通配绑定符号
			pt := reflect.TypeOf(params[k])
			pv := reflect.ValueOf(params[k])
			if pt.Kind() == reflect.Slice { // 数组或者切片
				l := pv.Len()
				if l > 0 {
					var sb bytes.Buffer
					for j := 0; j < l; j++ {
						sb.WriteString(fmt.Sprintf(":%d,", rIdx))
						rArgs = append(rArgs, pv.Index(j).Interface())
						rIdx++
					}
					sb.Truncate(sb.Len() - 1)
					list[v] = strings.Replace(list[v], "?", sb.String(), -1) // 替换通配占位符
				} else {
					list[v] = strings.Replace(list[v], "?", fmt.Sprintf(":%d", rIdx), -1) // 替换通配占位符
					rArgs = append(rArgs, params[k])
					rIdx++
				}
			} else { // 普通单一类型
				list[v] = strings.Replace(list[v], "?", fmt.Sprintf(":%d", rIdx), -1) // 替换通配占位符
				rArgs = append(rArgs, params[k])
				rIdx++
			}
		} else { // 指定变量
			rArgs = append(rArgs, params[k])
			rIdx++
		}
	}

	return &BuildResult{
		ExecSql: strings.Join(list, " "),
		Params:  rArgs,
	}, nil
}

func (oraDB *OraDB) QueryEntity(ctx context.Context, entity interface{}, execSql string, args ...any) error {
	row := oraDB.QueryRowContext(ctx, execSql, args...)
	return row.Scan(parseEntity2ColumnsV1(entity)...)
}

func (oraDB *OraDB) FindOne(ctx context.Context, entity interface{}, tableName, where string, args ...any) error {
	br, err := oraDB.BuildQuery(ctx, entity,
		tableName, where, args...)
	if err != nil {
		oraDB.logger.Errorc(ctx, "BuildQuery err:%v", err)
		return err
	}
	oraDB.logger.Infoc(ctx, "FindOne 执行SQL[%s][%+v]", br.ExecSql, br.Params)

	rows, err := oraDB.QueryContext(ctx, br.ExecSql, br.Params...)
	if err != nil {
		oraDB.logger.Errorc(ctx, "QueryContext 执行SQL失败[%s][%s]", br.ExecSql, err)
		return err
	}
	defer func() {
		rows.Close()
	}()
	cols, _ := rows.Columns()

	if !rows.Next() {
		return sql.ErrNoRows
	}

	return rows.Scan(parseEntity2Columns(entity, cols)...)
}

func (oraDB *OraDB) FindRows(ctx context.Context, entity interface{},
	tableName, where string, args ...any) (*sql.Rows, error,
) {
	br, err := oraDB.BuildQuery(ctx, entity,
		tableName, where, args...)
	if err != nil {
		oraDB.logger.Errorc(ctx, "BuildQuery got err:%v", err)
		return nil, err
	}

	oraDB.logger.Infoc(ctx, "FindRows 执行SQL[%s][%+v]", br.ExecSql, br.Params)
	rows, err := oraDB.QueryContext(ctx, br.ExecSql, br.Params...)
	if err != nil {
		oraDB.logger.Errorc(ctx, "QueryContext 执行SQL失败[%s][%s]", br.ExecSql, err)
		return rows, err
	}

	return rows, nil
}

func (oraDB *OraDB) ScanRows(rows *sql.Rows, entity interface{}) error {
	cols, err := rows.Columns()
	if err != nil {
		return err
	}
	err = rows.Scan(parseEntity2Columns(entity, cols)...)
	if err != nil {
		return err
	}
	return nil
}

func (oraDB *OraDB) ScanRows2Map(rows *sql.Rows) (map[string]interface{}, error) {
	cols, err := rows.Columns()
	if err != nil {
		return nil, err
	}
	cache := make([]interface{}, len(cols))
	for i := range cols {
		var val interface{}
		cache[i] = &val
	}
	err = rows.Scan(cache...)
	if err != nil {
		return nil, err
	}

	r := make(map[string]interface{}, len(cols))
	for i, c := range cols {
		r[c], _ = ConvertType(cache[i])
	}

	return r, nil
}

func (oraDB *OraDB) CreateEntityV1(ctx context.Context, entity interface{}) (int, error) {
	tableInfo, err := CreateTableInfo(entity)
	if err != nil {
		oraDB.logger.Errorc(ctx, "CreateTableInfo get err:%v", err)
		return 0, errors.Errorf("entity can not parse into table struct")
	}

	tableName := ""
	if oraDB.schema != "" {
		tableName = oraDB.schema + "." + tableInfo.TableName
	} else {
		tableName = tableInfo.TableName
	}

	insertSql := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s) ",
		tableName,
		strings.Join(tableInfo.Columns, ","),
		strings.Join(tableInfo.PlaceHolders, ","))

	oraDB.logger.Debugc(ctx, "INSERT SQL[%s]", insertSql)

	if tx := oraDB.GetCtxTx(ctx); tx != nil {
		r, err := tx.ExecContext(ctx, insertSql, entity)
		if err != nil {
			// oraDB.logger.Errorc(ctx, "tx.ExecContext err[%s]", err)
			return 0, err
		}
		n, _ := r.RowsAffected()

		return int(n), nil
	}
	r, err := oraDB.ExecContext(ctx, insertSql, entity)
	if err != nil {
		// oraDB.logger.Errorc(ctx, "db.ExecContext err[%s]", err)
		return 0, err
	}
	n, _ := r.RowsAffected()

	oraDB.logger.Infoc(ctx, "INSERT SQL[%s]插入[%d]", insertSql, n)

	return int(n), nil
}

func (oraDB *OraDB) CreateEntityV2(ctx context.Context, tableName string, entity map[string]interface{}) (int, error) {
	if oraDB.schema != "" {
		tableName = oraDB.schema + "." + tableName
	}

	columns := make([]string, len(entity))
	placeHolders := make([]string, len(entity))
	args := make([]interface{}, len(entity))

	idx := 0
	for colName, val := range entity {
		columns[idx] = colName
		placeHolders[idx] = ":" + colName
		args[idx] = val
		idx++
	}

	insertSql := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s) ",
		tableName,
		strings.Join(columns, ","),
		strings.Join(placeHolders, ","))

	// oraDB.logger.Infoc(ctx, "INSERT SQL[%s]", insertSql)

	if tx := oraDB.GetCtxTx(ctx); tx != nil {
		r, err := tx.ExecContext(ctx, insertSql, args...)
		if err != nil {
			// oraDB.logger.Errorc(ctx, "tx.ExecContext err[%s]", err)
			return 0, err
		}
		n, _ := r.RowsAffected()

		return int(n), nil
	}
	r, err := oraDB.ExecContext(ctx, insertSql, args...)
	if err != nil {
		// oraDB.logger.Errorc(ctx, "db.ExecContext err[%s]", err)
		return 0, err
	}
	n, _ := r.RowsAffected()

	oraDB.logger.Infoc(ctx, "INSERT SQL[%s]插入[%d]", insertSql, n)

	return int(n), nil
}

func CreateTableInfo(entity interface{}) (*TableInfo, error) {
	et := reflect.TypeOf(entity)
	var entityValue interface{}
	if et.Kind() == reflect.Slice {
		entityValue = reflect.ValueOf(entity).Index(0).Interface()
	} else if et.Kind() == reflect.Ptr {
		return nil, errors.Errorf("invalid pointer struct")
	} else {
		entityValue = entity
	}

	key := reflect.TypeOf(entityValue)
	if v, ok := tableInfoCreate.Load(key); ok {
		tableInfo := v.(*TableInfo)
		return tableInfo, nil
	}

	tableInfo, err := parseEntityTableInfo(entityValue)
	if err != nil {
		return nil, err
	}

	tableInfoCreate.Store(key, tableInfo)
	return tableInfo, nil
}

func QueryTableInfo(entity interface{}) (*TableInfo, error) {
	key := reflect.TypeOf(entity)
	if v, ok := tableInfoQuery.Load(key); ok {
		tableInfo := v.(*TableInfo)
		return tableInfo, nil
	}

	tableInfo, err := parseEntityTableInfo(entity)
	if err != nil {
		return nil, err
	}

	columns := make([]string, 0)

	for idx, item := range tableInfo.Columns {
		if tableInfo.Scope[idx] == "local" {
			continue
		}
		columns = append(columns, item)
	}
	tableInfo.QueryColumns = columns

	tableInfoQuery.Store(key, tableInfo)
	return tableInfo, nil
}

// 类型转换方法
func ConvertType(data interface{}) (interface{}, string) {
	item := *data.(*interface{})
	switch item.(type) {
	case nil: // 空值
		return nil, ""
	case []uint8: // 字符串
		return string(item.([]uint8)), "string"
	case time.Time: // 时间类型
		return item.(time.Time).UnixNano(), "time"
	case int, int8, int16, int32, int64, float32, float64, byte: // 数字型
		return item, "number"
	case bool: // 布尔型
		return item, "bool"
	default:
		return item, ""
	}
}
